package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotlenewService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotlenewServiceImpl implements IGetTotlenewService {
    public String getsqlDepartmentnew(SearchWholeHospitalInfo searchInfo, String classification, String condition,String condition1,String conditionOne,String conditionTwo,int i, List<SearchWholeHospitalInfo> list1) {
        String arrivalStore = "";
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore()) && !"请选择".equals(searchInfo.getArrivalStore())) {
            String arrivalStore2[] = searchInfo.getArrivalStore().split(",");
            if (arrivalStore2.length == 1) {
                arrivalStore = arrivalStore2[0];
            } else {
                for (int j = 0; j < arrivalStore2.length; j++) {
                    if (j == (arrivalStore2.length - 1)) {
                        arrivalStore += "'" + arrivalStore2[j];
                    } else if (j == 0) {
                        arrivalStore += arrivalStore2[j] + "'" + ",";
                    } else {
                        arrivalStore += "'" + arrivalStore2[j] + "'" + ",";
                    }
                }
            }
        }
        String sqlb="";
        String performance="hospital_performance";
        if (StringUtil.isNotEmpty(searchInfo.getDimensionSelectionCustomerAll())) {
            if ("ks".equals(searchInfo.getDimensionSelectionCustomerAll())){
                sqlb+="";
            }else{
                sqlb+=" and b.treatment_unit='"+searchInfo.getDimensionSelectionCustomerAll()+"'";
            }
            performance="section_performance";
        }
        String sql="select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status='新客首次' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition+sqlb+" group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and b.customer_status='新客首次'  and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and "+condition+sqlb+" group by b.reservations_id) t) AS volume," +
                "ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition1+sqlb+" and b.customer_status='新客首次' group by c.id) t),0.00) as departmentPerformance ," +
                " 0 as tcPerformance from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status='新客多次' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition+sqlb+" group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and (b.customer_status='新客多次' or b.customer_status='新客N次')  and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and "+condition+sqlb+" group by b.reservations_id) t) AS volume," +
                "ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition1+sqlb+" and (b.customer_status='新客多次' or b.customer_status='新客N次') group by c.id) t),0.00) as departmentPerformance ," +
                " 0 as tcPerformance from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status in ('新客首次','新客多次') and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition+sqlb+" group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and b.customer_status in ('新客首次','新客多次','新客N次') and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and "+condition+sqlb+" group by b.reservations_id) t) AS volume," +
                "ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition1+sqlb+" and b.customer_status in ('新客首次','新客多次','新客N次') group by c.id) t),0.00) as departmentPerformance ," +
                " 0 as tcPerformance from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status='老客' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition+sqlb+" group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and b.customer_status='老客'  and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and "+condition+sqlb+" group by b.reservations_id) t) AS volume," +
                "ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition1+sqlb+" and b.customer_status='老客' group by c.id) t),0.00) as departmentPerformance ," +
                " 0 as tcPerformance from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status in ('新客首次','新客多次','老客') and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition+sqlb+" group by b.reservations_id) t) AS outpatientVolume," +
                "(select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and b.customer_status in ('新客首次','新客多次','老客','新客N次')  and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "')  and "+condition+sqlb+" group by b.reservations_id) t) AS volume," +
                "ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '" + searchInfo.getStartTime() + "' and c.time <= '" + searchInfo.getEndTime() + "' and b.abbreviation in ('" + arrivalStore + "') and "+condition1+sqlb+" and b.customer_status in ('新客首次','新客多次','老客','新客N次') group by c.id) t),0.00) as departmentPerformance ," +
                " 0 as tcPerformance from dual";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getTotleSqlDepartmentnew(SearchWholeHospitalInfo searchInfo) {
        String arrivalStore = "";
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore()) && !"请选择".equals(searchInfo.getArrivalStore())) {
            String arrivalStore2[] = searchInfo.getArrivalStore().split(",");
            if (arrivalStore2.length == 1) {
                arrivalStore = arrivalStore2[0];
            } else {
                for (int j = 0; j < arrivalStore2.length; j++) {
                    if (j == (arrivalStore2.length - 1)) {
                        arrivalStore += "'" + arrivalStore2[j];
                    } else if (j == 0) {
                        arrivalStore += arrivalStore2[j] + "'" + ",";
                    } else {
                        arrivalStore += "'" + arrivalStore2[j] + "'" + ",";
                    }
                }
            }
        }
        String sqlb=" ";
        String performance="hospital_performance";
        if (StringUtil.isNotEmpty(searchInfo.getDimensionSelectionCustomerAll())) {
            if ("ks".equals(searchInfo.getDimensionSelectionCustomerAll())){
                sqlb+="";
            }else{
                sqlb+=" and b.treatment_unit='"+searchInfo.getDimensionSelectionCustomerAll()+"'";
            }
            performance="section_performance";
        }
        String sql = "select '合计' as rirstClassification," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status='新客首次' and b.time>='"+searchInfo.getStartTime()+"' " +
                " and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+" " +
                " and b.abbreviation in ('" + arrivalStore + "') group by b.reservations_id) t) AS outpatientVolume," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and b.customer_status='新客首次' " +
                " and b.time>='"+searchInfo.getStartTime()+"' and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+"  and b.abbreviation in ('" + arrivalStore + "') " +
                " group by b.reservations_id) t) AS volume," +
                " ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join " +
                " customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '"+searchInfo.getStartTime()+"' and c.time <= '"+searchInfo.getEndTime()+"' " +
                " and b.customer_status='新客首次' and b.abbreviation in ('" + arrivalStore + "') group by c.id) t),0.00) as departmentPerformance," +
                " 0 as tcPerformance from dual  " +
                " union all" +
                " select '合计' as rirstClassification," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status='新客多次' and b.time>='"+searchInfo.getStartTime()+"' " +
                " and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+" " +
                " and b.abbreviation in ('" + arrivalStore + "') group by b.reservations_id) t) AS outpatientVolume," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and (b.customer_status='新客多次' or b.customer_status='新客N次') " +
                " and b.time>='"+searchInfo.getStartTime()+"' and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+"  and b.abbreviation in ('" + arrivalStore + "') " +
                " group by b.reservations_id) t) AS volume," +
                " ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join " +
                " customer_status_statistics b on b.customer_id=c.customer_id  where c.time >= '"+searchInfo.getStartTime()+"' and c.time <= '"+searchInfo.getEndTime()+"' " +
                " and (b.customer_status='新客多次' or b.customer_status='新客N次') and b.abbreviation in ('" + arrivalStore + "') group by c.id) t),0.00) as departmentPerformance," +
                " 0 as tcPerformance from dual  " +
                " union all" +
                " select '合计' as rirstClassification," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status in ('新客首次','新客多次') and b.time>='"+searchInfo.getStartTime()+"' " +
                " and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+" " +
                " and b.abbreviation in ('" + arrivalStore + "') group by b.reservations_id) t) AS outpatientVolume," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and b.customer_status in ('新客首次','新客多次','新客N次') " +
                " and b.time>='"+searchInfo.getStartTime()+"' and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+"  and b.abbreviation in ('" + arrivalStore + "')  " +
                " group by b.reservations_id) t) AS volume," +
                " ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join customer_status_statistics b" +
                " on b.customer_id=c.customer_id  where c.time >= '"+searchInfo.getStartTime()+"' and c.time <= '"+searchInfo.getEndTime()+"' and b.customer_status in ('新客首次','新客多次','新客N次') " +
                " and b.abbreviation in ('" + arrivalStore + "') group by c.id) t),0.00) as departmentPerformance," +
                " 0 as tcPerformance from dual  " +
                " union all" +
                " select '合计' as rirstClassification," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status='老客' and b.time>='"+searchInfo.getStartTime()+"' " +
                " and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+" " +
                " and b.abbreviation in ('" + arrivalStore + "') group by b.reservations_id) t) AS outpatientVolume," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and b.customer_status='老客' " +
                " and b.time>='"+searchInfo.getStartTime()+"' and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+"  and b.abbreviation in ('" + arrivalStore + "') " +
                " group by b.reservations_id) t) AS volume," +
                " ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join customer_status_statistics b" +
                " on b.customer_id=c.customer_id  where c.time >= '"+searchInfo.getStartTime()+"' and c.time <= '"+searchInfo.getEndTime()+"'   and b.customer_status='老客' " +
                " and b.abbreviation in ('" + arrivalStore + "') group by c.id) t),0.00) as departmentPerformance," +
                " 0 as tcPerformance from dual  " +
                " union all" +
                " select '合计' as rirstClassification," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.customer_status in ('新客首次','新客多次','老客') and b.time>='"+searchInfo.getStartTime()+"' " +
                " and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+" " +
                " and b.abbreviation in ('" + arrivalStore + "') group by b.reservations_id) t) AS outpatientVolume," +
                " (select count(1) from (select b.customer_id from customer_status_statistics b where b.isdeal_status='已成交' and b.customer_status in ('新客首次','新客多次','老客','新客N次') " +
                " and b.time>='"+searchInfo.getStartTime()+"' and b.time<='"+searchInfo.getEndTime()+"' "+sqlb+"  and b.abbreviation in ('" + arrivalStore + "') " +
                " group by b.reservations_id) t) AS volume," +
                " ifnull((select sum(t."+performance+") from (select c."+performance+" from hospital_section_statistics c left join customer_status_statistics b" +
                " on b.customer_id=c.customer_id  where c.time >= '"+searchInfo.getStartTime()+"' and c.time <= '"+searchInfo.getEndTime()+"' and b.customer_status in ('新客首次','新客多次','老客','新客N次') " +
                " and b.abbreviation in ('" + arrivalStore + "') group by c.id) t),0.00) as departmentPerformance," +
                " 0 as tcPerformance from dual ";
        return sql;
    }
}